package sg.edu.nus.dashboard.service.report;

import java.awt.Font;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.FontFactory;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

import sg.edu.nus.dashboard.entity.OpdDBStat;
import sg.edu.nus.dashboard.service.dbconn.DBConstants;
import sg.edu.nus.dashboard.service.stat.DatabaseStatService;

public class ReportServlet extends HttpServlet {

	private static final long serialVersionUID = -1550926729000395210L;

	private Date fromTime;
	private Date toTime;
	private List<OpdDBStat> list;
	SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm");

	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		this.doDispatch(req, resp);
	}

	public void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		this.doDispatch(req, resp);
	}

	private void doDispatch(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		resp.reset();
		req.setCharacterEncoding("UTF-8");
		resp.setCharacterEncoding("UTF-8");

		String param = req.getParameter("type");
		String fromTimeStr = req.getParameter("fromTime");
		String toTimeStr = req.getParameter("toTime");

		// construct time range
		try {
			fromTime = dateFormat.parse(fromTimeStr);
			toTime = dateFormat.parse(toTimeStr);
		} catch (ParseException e) {
			e.printStackTrace();
		}

		// get all statistic data
		list = (new DatabaseStatService()).getAllStatByTime(fromTime, toTime);

		System.out.println(list.size());

		if (param.equals("pdf")) {
			this.genPDFReport(resp, list);
		} else if (param.equals("excel")) {
			this.genExcelReport(resp, list);
		} else {
			this.genHTMLReport(resp, list);
		}

	}

	private void genPDFReport(HttpServletResponse resp, List<OpdDBStat> list)
			throws IOException {

		resp.setContentType("application/pdf; charset=UTF-8");
		resp.setHeader("Content-Disposition", "report.pdf");
		resp.setHeader("Cache-Control", "no-cache");
		try {
			Document doc = new Document();
			ByteArrayOutputStream ba = new ByteArrayOutputStream();

			PdfWriter writer = PdfWriter.getInstance(doc, ba);
			doc.open();

			// create header
			Paragraph header = new Paragraph(
					"ORACLE PERFORMANCE STATISTIC REPORT", FontFactory.getFont(
							FontFactory.HELVETICA, 14, Font.BOLD));
			doc.add(header);
			doc
					.add(new Paragraph(
							"---------------------------------------------------------"
									+ "-----------------------------------------------------------------------"));
			doc.add(new Paragraph(" "));

			// create database info header
			doc.add(new Paragraph(
					"IP ADDRESS       PORT       INSTANCE       USER",
					FontFactory.getFont(FontFactory.HELVETICA, 11, Font.BOLD)));
			String infoStr = DBConstants.IP + "                "
					+ DBConstants.PORT + "          " + DBConstants.INSTANCE
					+ "                 " + DBConstants.USERNAME;
			doc.add(new Paragraph(infoStr, FontFactory.getFont(
					FontFactory.HELVETICA, 11)));
			doc.add(new Paragraph(" "));
			doc.add(new Paragraph(" "));

			// create report info
			doc.add(new Paragraph("START TIME                 END TIME",
					FontFactory.getFont(FontFactory.HELVETICA, 11, Font.BOLD)));
			infoStr = dateFormat.format(this.fromTime) + "          "
					+ dateFormat.format(this.toTime);
			doc.add(new Paragraph(infoStr, FontFactory.getFont(
					FontFactory.HELVETICA, 11)));
			doc.add(new Paragraph(" "));
			doc.add(new Paragraph(" "));

			// create statistics
			PdfPTable table = new PdfPTable(9);
			table.addCell("DB_ID");
			table.addCell("STAT_TIME");
			table.addCell("OVERALL_HEALTH");
			table.addCell("BUFFER_CACHE");
			table.addCell("SHARED_POOL");
			table.addCell("REDO_LOG_BUFFER");
			table.addCell("SORTING_MEMORY_AREA");
			table.addCell("ROLLBACK_SEGMENTS");
			table.addCell("TABLE_SPACE");

			for (int l = 0; l < list.size(); l++) {
				table.addCell(list.get(l).getDbId() + "");
				table.addCell(dateFormat.format(list.get(l).getStatTime()));
				table.addCell(list.get(l).getOverallHealth() + "");
				table.addCell(list.get(l).getBufferCache() + "");
				table.addCell(list.get(l).getSharedPool() + "");
				table.addCell(list.get(l).getRedoLogBuffer() + "");
				table.addCell(list.get(l).getSortingMemoryArea() + "");
				table.addCell(list.get(l).getRollbackSegment() + "");
				table.addCell(list.get(l).getTableSpace() + "");
			}

			doc.add(table);

			doc.close();
			resp.setContentLength(ba.size());
			ServletOutputStream out = resp.getOutputStream();
			ba.writeTo(out);
			out.flush();
			out.close();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
	}

	private void genExcelReport(HttpServletResponse resp, List<OpdDBStat> list)
			throws IOException {

		resp.setContentType("application/vnd.ms-excel; charset=UTF-8");
		resp.setHeader("Content-Disposition", "report.xls");
		resp.setHeader("Cache-Control", "no-cache");

		int i = 0; // index of column
		int j = 0; // index of row
		HSSFWorkbook workbook = new HSSFWorkbook();

		// generate a sheet
		HSSFSheet sheet = workbook.createSheet("report");

		// set default column width
		sheet.setDefaultColumnWidth(15);

		// create header
		HSSFRow row = sheet.createRow(i);
		HSSFCell cell = row.createCell(j);
		HSSFRichTextString text = new HSSFRichTextString(
				"ORACLE PERFORMANCE STATISTIC REPORT");
		cell.setCellValue(text);

		// create database info
		String[] dbInfoHeader = { "IP ADDRESS", "PORT", "INSTANCE", "USER" };
		i = i + 2;
		row = sheet.createRow(i);
		for (int k = 0; k < dbInfoHeader.length; k++) {
			cell = row.createCell(j);
			text = new HSSFRichTextString(dbInfoHeader[k]);
			cell.setCellValue(text);
			j++;
		}

		String[] dbInfo = { DBConstants.IP, DBConstants.PORT,
				DBConstants.INSTANCE, DBConstants.USERNAME };
		i++;
		j = 0;
		row = sheet.createRow(i);
		for (int k = 0; k < dbInfo.length; k++) {
			cell = row.createCell(j);
			text = new HSSFRichTextString(dbInfo[k]);
			cell.setCellValue(text);
			j++;
		}

		// create report info
		String[] reportInfoHeader = { "BEGIN TIME", "END TIME" };
		i = i + 2;
		j = 0;
		row = sheet.createRow(i);
		for (int k = 0; k < reportInfoHeader.length; k++) {
			cell = row.createCell(j);
			text = new HSSFRichTextString(reportInfoHeader[k]);
			cell.setCellValue(text);
			j++;
		}

		String[] reportInfo = { dateFormat.format(this.fromTime),
				dateFormat.format(this.toTime) };
		i++;
		j = 0;
		row = sheet.createRow(i);
		for (int k = 0; k < reportInfo.length; k++) {
			cell = row.createCell(j);
			text = new HSSFRichTextString(reportInfo[k]);
			cell.setCellValue(text);
			j++;
		}

		// create statistics
		String[] statisticHeader = { "DB_ID", "STAT_TIME", "OVERALL_HEALTH",
				"BUFFER_CACHE", "SHARED_POOL", "REDO_LOG_BUFFER",
				"SORTING_MEMORY_AREA", "ROLLBACK_SEGMENTS", "TABLE_SPACE" };
		i = i + 2;
		j = 0;
		row = sheet.createRow(i);
		for (int k = 0; k < statisticHeader.length; k++) {
			cell = row.createCell(j);
			text = new HSSFRichTextString(statisticHeader[k]);
			cell.setCellValue(text);
			j++;
		}

		for (int l = 0; l < list.size(); l++) {
			i++;
			j = 0;
			list.get(l).getDbId();

			String[] temp = { list.get(l).getDbId() + "",
					dateFormat.format(list.get(l).getStatTime()),
					list.get(l).getOverallHealth() + "",
					list.get(l).getBufferCache() + "",
					list.get(l).getSharedPool() + "",
					list.get(l).getRedoLogBuffer() + "",
					list.get(l).getSortingMemoryArea() + "",
					list.get(l).getRollbackSegment() + "",
					list.get(l).getTableSpace() + "" };

			row = sheet.createRow(i);
			for (int k = 0; k < temp.length; k++) {
				cell = row.createCell(j);
				text = new HSSFRichTextString(temp[k]);
				cell.setCellValue(text);
				j++;
			}
		}

		workbook.write(resp.getOutputStream());
		resp.getOutputStream().flush();
		resp.getOutputStream().close();

	}

	private void genHTMLReport(HttpServletResponse resp, List<OpdDBStat> list)
			throws IOException {
		resp.setContentType("text/xml; charset=UTF-8");
		resp.setHeader("Content-Disposition", "report.xml");
		resp.setHeader("Cache-Control", "no-cache");

		
	}
}
